<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class songshu_init_data extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'songshu:init_data {day?}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '初始化每天文章采集数和入库 可选参数day Ymd格式';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $day = $this->argument('day');
        if(empty($day)){
            $day = date('Ymd',strtotime("-1 day"));
        }
           
        $curent_day = $day;  
        $begin_time = strtotime($curent_day);
        $end_day = date('Y-m-d 23:59:59',$begin_time);
        $end_time = strtotime($end_day);
        #采集数
        $collection = DB::connection('db2')->table('article')
               ->select(DB::raw('type,count(id) as count'))->whereBetween('day',[$begin_time,$end_time])     
               ->groupBy('type')
               ->get();
        #入库
        $insert = DB::table('article')
               ->select(DB::raw('type,count(id) as count'))->where('status',0)->whereBetween('day',[$begin_time,$end_time])
               ->whereIn('type_of_article',[1,2])      
               ->groupBy('type')
               ->get();
        $data = [];
        foreach($collection as $key=>$item){
            $data[] = [
                'cat_id'=>$item->type,
                'type'=>0,
                'ymd'=>$curent_day,
                'ym'=>date('Ym',$begin_time),
                'collection_number'=>$item->count,
            ];
        }

         foreach($data as $d){
            $exists = DB::table('check_tongji')->where([
                'cat_id'=>$d['cat_id'],
                'type'=>$d['type'],
                'ymd'=>$d['ymd'],
            ])->exists();
            if($exists){
                DB::table('check_tongji')->where([
                    'cat_id'=>$d['cat_id'],
                    'type'=>$d['type'],
                    'ymd'=>$d['ymd'],
                ])->update([
                    'collection_number'=>$d['collection_number'],
                ]);
            }else{
                DB::table('check_tongji')->insert($d);
            }
        }
        
        foreach($insert as $item){
            $exists = DB::table('check_tongji')->where([
                'ymd'=>$curent_day,
                'cat_id'=>$item->type,
                'type'=>0,
            ])->exists();
            if($exists){
                DB::table('check_tongji')->where([
                    'ymd'=>$curent_day,
                    'cat_id'=>$item->type,
                    'type'=>0,
                ])->update([
                    'insert_number'=>$item->count,
                ]);
            }else{
                DB::table('check_tongji')->insert([
                    'cat_id'=>$item->type,
                    'type'=>0,
                    'ymd'=>$curent_day,
                    'ym'=>date('Ym',$begin_time),
                    'insert_number'=>$item->count,
                ]);
            }
        }

        $this->video();
    }


    public function video(){
        $day = $this->argument('day');
        if(empty($day)){
            $day = date('Ymd',strtotime("-1 day"));
        }
           
        $curent_day = $day;  
        $begin_time = strtotime($curent_day);
        $end_day = date('Y-m-d 23:59:59',$begin_time);
        $end_time = strtotime($end_day);
        #采集数
        $collection = DB::connection('db2')->table('video')
               ->select(DB::raw('type,count(id) as count'))->whereBetween('day',[$begin_time,$end_time])      
               ->groupBy('type')
               ->get();
        #入库
        $insert = DB::table('article')
               ->select(DB::raw('type,count(id) as count'))->where('status',0)->whereBetween('day',[$begin_time,$end_time])
               ->whereIn('type_of_article',[3])      
               ->groupBy('type')
               ->get();
        $data = [];
        foreach($collection as $key=>$item){
            $data[] = [
                'cat_id'=>$item->type,
                'type'=>1,
                'ymd'=>$curent_day,
                'ym'=>date('Ym',$begin_time),
                'collection_number'=>$item->count,
            ];
        }
        
        foreach($data as $d){
            $exists = DB::table('check_tongji')->where([
                'cat_id'=>$d['cat_id'],
                'type'=>$d['type'],
                'ymd'=>$d['ymd'],
            ])->exists();
            if($exists){
                DB::table('check_tongji')->where([
                    'cat_id'=>$d['cat_id'],
                    'type'=>$d['type'],
                    'ymd'=>$d['ymd'],
                ])->update([
                    'collection_number'=>$d['collection_number'],
                ]);
            }else{
                DB::table('check_tongji')->insert($d);
            }
        }
        
        foreach($insert as $item){
            $exists = DB::table('check_tongji')->where([
                'ymd'=>$curent_day,
                'cat_id'=>$item->type,
                'type'=>1,
            ])->exists();
            if($exists){
                DB::table('check_tongji')->where([
                    'ymd'=>$curent_day,
                    'cat_id'=>$item->type,
                    'type'=>1,
                ])->update([
                    'insert_number'=>$item->count,
                ]);
            }else{
                DB::table('check_tongji')->insert([
                    'cat_id'=>$item->type,
                    'type'=>1,
                    'ymd'=>$curent_day,
                    'ym'=>date('Ym',$begin_time),
                    'insert_number'=>$item->count,
                ]);
            }
        }
    }
}
